package com.chu.cloud.util;

import com.chu.cloud.vo.UserVo;
import org.apache.catalina.User;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * describe：Excel文档操作工具类，支持.xls .xlsx excel文件的读、写操作<br/>
 * 使用poi.jar实现
 */
public class ExcelPoiUtils {
    public static enum Type {
        XLS,
        XLSX
    }

    /**
     * @param file excel类型文件
     * @return excel文件的内容
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static List<String[]> readExcelFromFile(File file) throws IOException, InvalidFormatException {
        Type type = getExcelType(file);
        List<String[]> contentList = new ArrayList<String[]>();
        if (Type.XLS.equals(type)) {
            readHSSF(contentList, new NPOIFSFileSystem(file)); // File, needs less memory
        } else {
            readXSSF(contentList, OPCPackage.open(file)); // File, needs less memory
        }
        return contentList;
    }

    /**
     * 推荐优先使用readExcelFromFile方法(内存相对占用更小)
     *
     * @param in       excel类型的输入流
     * @param fileType
     * @return 从输入流读取的excel文件的内容
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static List<String[]> readExcelFromInputStream(InputStream in, Type excelType) throws IOException, InvalidFormatException {
        List<String[]> contentList = new ArrayList<String[]>();
        if (Type.XLS.equals(excelType)) {
            readHSSF(contentList, new NPOIFSFileSystem(in)); // InputStream, needs more memory
        } else {
            readXSSF(contentList, OPCPackage.open(in)); // InputStream, needs more memory
        }
        //in.close();
        return contentList;
    }

    /**
     * 把内容写到excel文件
     *
     * @param file       要生成的Excel文件
     * @param title      标题内容，如为null则不显示标题
     * @param contenList 内容列表，列表中的元素类型为String[]
     * @throws IOException
     */
    public static void writeToExcelFile(File file, String title, List<String[]> contenList) throws IOException {
        Type type = getExcelType(file);
        OutputStream out = new FileOutputStream(file);
        writeToExcelOutputStream(out, title, contenList, type);
    }

    /**
     * 把内容写到输出流
     *
     * @param out        Excel文件输出流
     * @param title      标题内容
     * @param contenList 内容列表，列表中的元素类型为String[]
     * @param fileType   excel文件类型,xls/xlsx
     * @throws IOException
     */
    public static void writeToExcelOutputStream(OutputStream out, String title, List<String[]> contenList, Type fileType) throws IOException {
        long begin = System.currentTimeMillis();
        if (Type.XLS.equals(fileType)) { //HSSFWorkbook
            HSSFWorkbook wb = new HSSFWorkbook();
            writeContent(out, title, contenList, wb);
        } else { //XSSFWorkbook
            XSSFWorkbook wb = new XSSFWorkbook();
            writeContent(out, title, contenList, wb);
        }
        out.flush();
        out.close();
        long end = System.currentTimeMillis();
        System.out.println("生成了一个excel文件," + (end - begin) / 1000 + "秒");
    }

    /**
     * 把Excel文件保存到用户浏览器端
     *
     * @param res        Excel文件输出流
     * @param filename   指定下载后保存的文件名称,可为空
     * @param title      内容中的标题
     * @param contenList 内容列表，列表中的元素类型为String[]
     * @param fileType   excel文件类型,xls/xlsx
     * @throws IOException
     */
    public static void writeToExcelOutputStream(HttpServletResponse res, String filename,
                                                String title, List<String[]> contenList, Type fileType) throws IOException {
        res.setContentType("application/vnd.ms-excel");
        if (StringUtils.isNotBlank(filename)) {
            if (filename.indexOf(".") == -1) {
                filename += "." + fileType.name().toLowerCase();
            }
            // 设置内容描述，支持中文文件名称
            res.setHeader("Content-Disposition", "attachment;fileName=" + new String(filename.getBytes("gb2312"), "ISO8859-1"));
        }
        writeToExcelOutputStream(res.getOutputStream(), title, contenList, fileType);
    }

    /**
     * 向输出流写入excel内容
     */
    private static void writeContent(OutputStream out, String title, List<String[]> contentList, Workbook wb) throws IOException {
        String time = new SimpleDateFormat("yyyy-MM-dd hh mm ss").format(new Date());
        String preSheetName = time + " sheet ";
        CellStyle style = wb.createCellStyle();
        Font f = wb.createFont();
        f.setFontHeightInPoints((short) 13);// 字号
        f.setBold(true);
        style.setFont(f);
        style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, contentList.get(0).length - 1);
        boolean hasTitle = StringUtils.isNotBlank(title);
        if (null != contentList && !contentList.isEmpty()) {
            final int excelMaxRow = 65535;// 一个excel表格sheet最多只能存放65535条记录
            int contentSize = contentList.size();
            int sheetNum = (contentSize / excelMaxRow) + ((contentSize % excelMaxRow) > 0 ? 1 : 0);
            for (int i = 0; i < sheetNum; i++) {
                Sheet sheet = wb.createSheet();
                wb.setSheetName(i, preSheetName + (i + 1));
                Row row = null;
                Cell cell = null;
                int index = 0;// 记录数是否达到excelMaxRow
                if (hasTitle) {
                    // region.setColumnTo((short)(contenList.get(0).length-1));
                    sheet.addMergedRegion(range);
                    // sheet.addMergedRegion(region);//合并第一行的单元格，用于设置标题
                    row = sheet.createRow(0);
                    cell = row.createCell(0);
                    cell.setCellValue(title);// 设置标题
                    cell.setCellStyle(style);
                    index += 1; // 因为标题占用了一行，所以从index+1开始插入行
                }
                while (!contentList.isEmpty()) {
                    row = sheet.createRow(index);
                    String[] content = contentList.remove(0);
                    int len = content.length;
                    for (int k = 0; k < len; k++) {
                        sheet.setColumnWidth(k, 7500);
                        cell = row.createCell(k);
                        cell.setCellValue(content[k]);
                    }
                    index++;
                    if (index % excelMaxRow == 0 || index > contentSize) {
                        index = 0;
                        break;
                    }
                }
                index = 0;
            }
        } else {
            title += "（无相关记录）";
            Sheet sheet = wb.createSheet();
            sheet.setColumnWidth(0, 9000);
            wb.setSheetName(0, preSheetName + 1);
            range.setLastColumn(10);
            sheet.addMergedRegion(range);
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(title);// 设置标题
            cell.setCellStyle(style);
        }
        wb.write(out);
    }

    /**
     * 读取xlsx文件的内容
     */
    @SuppressWarnings("resource")
    private static void readXSSF(List<String[]> contentList, OPCPackage pkg) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        int sheetNum = wb.getNumberOfSheets();
        for (int index = 0; index < sheetNum; index++) {
            XSSFSheet xssfSheet = wb.getSheetAt(index);
            for (Row row : xssfSheet) {
                int cellNum = row.getLastCellNum();
                String[] cells = new String[cellNum];
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    cells[j] = getCellContent(cell);
                }
                contentList.add(cells);
            }
        }
        pkg.close();
    }

    /**
     * 读取xls文件的内容
     */
    @SuppressWarnings("resource")
    private static void readHSSF(List<String[]> contentList, NPOIFSFileSystem fs) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fs.getRoot(), false);
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int index = 0; index < sheetNum; index++) {
            HSSFSheet sheet = hssfWorkbook.getSheetAt(index);
            for (Row row : sheet) {
                int cellNum = row.getLastCellNum();
                String[] cells = new String[cellNum];
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    cells[j] = getCellContent(cell);
                }
                contentList.add(cells);
            }
        }
        fs.close();
    }

    /**
     * 获取单元格的内容，转换为String类型
     */
    public static String getCellContent(Cell cell) {
        String content = null;
        if (cell == null)
            return null;
        CellType cellType = cell.getCellTypeEnum();

        if (CellType.NUMERIC == cellType && DateUtil.isCellDateFormatted(cell)) {
            content = cell.getDateCellValue().getTime() + ""; //将时间转换为long，以字符串形式返回
        } else {
            cell.setCellType(CellType.STRING);
            content = cell.getRichStringCellValue().getString();
        }
        cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case STRING:
                content = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    content = cell.getDateCellValue().getTime() + "";
                } else {
                    content = cell.getNumericCellValue() + "";
                }
                break;
            case BOOLEAN:
                content = cell.getBooleanCellValue() + "";
                break;
            case FORMULA:
                content = cell.getCellFormula();
                break;
            default:
                content = cell.toString();
        }
        return content;
    }

    public static Type getExcelType(File file) {
        String fileName = file.getName();
        return getExcelType(fileName);
    }

    public static Type getExcelType(String fileName) {
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx"))
            throw new RuntimeException("excel file name must end with '.xls' or '.xlsx'");
        Type type = Type.XLS;
        if (fileName.endsWith(".xlsx"))
            type = Type.XLSX;
        return type;
    }


    /**
     * 导出Excel数据
     *
     * @param response
     * @param list
     * @param head
     * @param fileName
     */
    public static void exportExcel(HttpServletResponse response, List<String> list,
                                   List<String> head, String fileName) {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet

        HSSFCellStyle cellStyle = workbook.createCellStyle();

        HSSFDataFormat format = workbook.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("yyyy年m月d日"));


        try {
            fileName = new String(fileName.getBytes("gb2312"), "iso8859-1");
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        try {
            // 创建第一行存放列头
            HSSFRow row0 = sheet.createRow(0);
            for (int i = 0; i < head.size(); i++) {
                HSSFCell cell0 = row0.createCell(i);
                cell0.setCellValue(head.get(i));
            }

            // 填写导出内容到excel表格
            for (int i = 1; i <= list.size(); i++) {
                row0 = sheet.createRow(i);
                String[] value = list.get(i - 1).split(",");
                for (short j = 0; j < value.length; j++) {
                    if (null != value[j] && !"".equals(value[j])) {
                        HSSFCell cell = row0.createCell(j);
                        cell.setCellValue(value[j]);
                    } else {
                        row0.createCell(j).setCellValue("");
                    }

                }
            }
        } catch (ArrayIndexOutOfBoundsException e) {
            e.printStackTrace();
        }

        try {
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) throws IOException, InvalidFormatException {
//        File file = new File("D:\\ms.xls");
//        //fileName = System.getProperty("user.dir") + File.separator + "wb3.xlsx";
//        List<String[]> contentList = new ArrayList<String[]>();
//        System.out.println(file.getAbsolutePath());
//        InputStream fin = new FileInputStream(file);
//        contentList = readExcelFromInputStream(fin, getExcelType(file));
//        List<UserVo> list = new ArrayList<>();
//        for (int j = 1; j < contentList.size(); j++) {
//            UserVo userVo = new UserVo();
//            String[] cells = contentList.get(j);
//            for (int i = 0; i < cells.length; i++) {
//                switch (i) {
//                    case 0:
//                        userVo.setAppSystem(cells[i]);
//                        break;
//                    case 5:
//                        userVo.setGrade(cells[i]);
//                    default:
//                        break;
//                }
//            }
//            list.add(userVo);
//        }
//        System.out.println(list);
    }

}